﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using Skynet.Data;

namespace SkynetMSSQLAccess
{

    public class MsSqlDataAccess : AbstractDataAccess
    {
        #region Constructor
        /// <summary>
        /// 默认构造函数
        /// </summary>
        public MsSqlDataAccess()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="conn">MsSql数据连接对象</param>
        public MsSqlDataAccess(SqlConnection conn)
        {
            this.m_DbConnection = conn;
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public MsSqlDataAccess(string connectionString)
        {
            this.m_DbConnection = new SqlConnection(connectionString);
        }

        #endregion

        #region IDataAccess 成员
        //重载父类关闭
        //连接池中重复调用时 当调用过于频繁 会造成 在执行SQL语句时 报出 内存不可写 的错误
        //目前处理方法是 当每次关闭时 重新实例化一个连接
        public override void Close()
        {
            base.Close();
            this.m_DbConnection = new SqlConnection(this.m_DbConnection.ConnectionString);
        }

        #region Support Property & method
        /// <summary>
        /// 返回数据库类型枚举成员(DatabaseType)
        /// </summary>
        public override DatabaseType DatabaseType
        {
            get { return DatabaseType.MSSQLServer; }
        }

        private SqlConnection m_DbConnection;
        /// <summary>
        /// 返回数据库连接接口
        /// </summary>
        public override IDbConnection DbConnection
        {
            get
            {
                return m_DbConnection;
            }
        }

        private SqlTransaction trans = null;
        /// <summary>
        /// 开始一个事务
        /// </summary>
        /// <returns>数据库事务接口</returns>
        public override IDbTransaction BeginTransaction()
        {
            trans = m_DbConnection.BeginTransaction();
            return trans;
        }

        #endregion Support Property & method

        #region ExecuteNonQuery
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">SQL语句的参数集合</param>
        /// <param name="row">DataRow对象</param>
        /// <param name="sqlStruct">SqlStruct对象,封装Sql语句及参数</param>
        /// <returns>执行SQL语句影响的行数</returns>
        public override int ExecuteNonQuery(CommandType commandType, string commandText, QueryParameterCollection commandParameters, DataRow row, SqlStruct sqlStruct)
        {
            int tmpValue = 0;
            base.LogOutPut(new Func<int>(() =>
            {
                SqlCommand cmd = new SqlCommand();
                if (sqlStruct == null)
                    PrepareCommand(cmd, commandType, commandText, commandParameters);
                else
                {
                    for (int i = 0; i < sqlStruct.ParamFields.Length; i++)
                    {
                        if (sqlStruct.ParamFields[i].ParamName.IndexOf('_') != 1)
                            commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName]);
                        else
                            commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName, DataRowVersion.Original]);
                    }
                    PrepareCommand(cmd, CommandType.Text, sqlStruct.SqlString, commandParameters);
                }
                tmpValue = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return tmpValue;
            }), "ExecuteNonQuery", commandText, commandParameters);

            return tmpValue;
        }
        #endregion ExecuteNonQuery

        #region ExecuteDataSet
        /// <summary>
        /// 执行SQL语句,返回DataSet对象.
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">SQL语句的参数集合</param>
        /// <param name="ds">DataSet对象</param>
        /// <param name="tableName">DataSet中表的名称</param>
        /// <returns>DataSet对象</returns>
        public override DataSet ExecuteDataset(CommandType commandType, string commandText, QueryParameterCollection commandParameters, DataSet ds, string tableName)
        {
            base.LogOutPut(new Func<int>(() =>
            {

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, commandType, commandText, commandParameters);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                if (Object.Equals(tableName, null) || (tableName.Length < 1))
                    da.Fill(ds);
                else
                    da.Fill(ds, tableName);

                for (int j = 0; j < cmd.Parameters.Count; j++)
                {
                    if (cmd.Parameters[j].Direction == ParameterDirection.Output)
                        commandParameters[j].Value = cmd.Parameters[j].Value;
                }

                cmd.Parameters.Clear();
                //Console.WriteLine(ds.Tables.Count.ToString());
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0].Rows.Count;
                }
                else
                {
                    return 0;
                }
            }), "ExecuteDataset", commandText, commandParameters);
            return ds;
        }

        public override DataSet ExecuteDataset(string commandText, QueryParameterCollection commandParameters, DataSet ds, int startRecord, int maxRecords, string tableName)
        {
            base.LogOutPut(new Func<int>(() =>
            {

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, CommandType.Text, commandText, commandParameters);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                if (Object.Equals(tableName, null) || (tableName.Trim() == string.Empty))
                    da.Fill(ds);
                else
                    da.Fill(ds, startRecord, maxRecords, tableName);
                cmd.Parameters.Clear();
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0].Rows.Count;
                }
                else
                {
                    return 0;
                }
            }), "ExecuteDataset", commandText, commandParameters);
            return ds;
        }

        #endregion ExecuteDataSet

        #region ExecuteReader
        /// <summary>
        /// 执行SQL语句,返回IDataReader接口
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>IDataReader</returns>
        public override IDataReader ExecuteReader(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            IDataReader dr = null;
            base.LogOutPut(new Func<int>(() =>
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, commandType, commandText, commandParameters);
                dr = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return -1;
            }), "ExecuteReader", commandText, commandParameters);

            return dr;
        }
        #endregion ExecuteReader

        #region ExecuteScalar
        /// <summary>
        /// 执行SQL语句,返回object接口
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>object</returns>
        public override object ExecuteScalar(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            object tmpValue = null;

            base.LogOutPut(new Func<int>(() =>
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, commandType, commandText, commandParameters);
                tmpValue = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return -1;
            }), "ExecuteScalar", commandText, commandParameters);

            return tmpValue;
        }
        #endregion ExecuteScalar

        #region ExecuteXmlReader
        /// <summary>
        /// 执行SQL语句,返回XmlReader对象
        /// </summary>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        /// <returns>XmlReader</returns>
        public override XmlReader ExecuteXmlReader(CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            XmlReader reader = null;
            base.LogOutPut(new Func<int>(() =>
            {

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, commandType, commandText, commandParameters);
                reader = cmd.ExecuteXmlReader();
                cmd.Parameters.Clear();
                return -1;
            }), "ExecuteXmlReader", commandText, commandParameters);

            return reader;
        }
        #endregion ExecuteXmlReader

        #endregion
        /// <summary>
        /// 处理SqlCommand对象,设置参数等信息.
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="commandType">CommandType对象</param>
        /// <param name="commandText">要执行的SQL语句</param>
        /// <param name="commandParameters">QueryParameter集合,SQL语句的参数集合</param>
        private void PrepareCommand(SqlCommand cmd, CommandType commandType, string commandText, QueryParameterCollection commandParameters)
        {
            commandText = commandText.Replace(":", "@");
            commandText = commandText.Replace("##", ":");
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            cmd.Connection = this.m_DbConnection;
            cmd.Transaction = trans;
            DbTypeEnum typeenum = new DbTypeEnum();
            if ((commandParameters != null) && (commandParameters.Count > 0))
            {
                for (int i = 0; i < commandParameters.Count; i++)
                {
                    cmd.Parameters.AddWithValue(commandParameters[i].ParameterName, commandParameters[i].Value ?? DBNull.Value);
                    cmd.Parameters[i].Direction = commandParameters[i].Direction;
                    //调用存储过程时需要指定参数的类型
                    if (cmd.CommandType == CommandType.StoredProcedure)
                        cmd.Parameters[i].SqlDbType = (SqlDbType)commandParameters[i].DbType;
                    //解决由于C#模式string 为NVARCHAR类型而数据库是varchar时。造成速度很慢
                    int type = typeenum.GetTypeEnum(cmd.Parameters[i].DbType.ToString());
                    if (type != 0)
                    {
                        cmd.Parameters[0].SqlDbType = (SqlDbType)type;
                    }
                }
            }
        }
    }
}
